const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");

// 配送统计
router.post('/deliver/gpCargos', async ctx => {
    let tenantId = ctx.user.tenantId;
    let billDate = ctx.request.body.billDate
    let billNo = ctx.request.body.billNo
    let excel = ctx.request.body.excel
    let deliverId = ctx.request.body.deliverId
    let strIds = await tool.relCompany(tenantId);
    if (strIds.length === 0) {
        return ctx.err('还没有关联配送公司');
    }
    let admin = await db.models['admin'].findOne({
        where: {
            id: deliverId
        }
    })
    let str = ' '
    if(admin) {
        str = ` and deliverNames like '%${admin.name}%' `
    }
    let bdate = ''
    if(billNo) {
        bdate = ` and invo.billNo like '%${billNo}%' `
    }
    else {
        bdate = ` and a.billDate between '${billDate[0]}' and '${billDate[1]}' `
    }
    let tids = strIds.join(',')
    let sql = `
        SELECT a.psNo,a.billDate, billNo,buName,deliverNames,licence,case when state=2 then N'已确认' else N'送货中' end state,invId,qty,invName,spec,unit2,unit1 from 
        (
            select a.psNo,a.billDate, a.billNo,a.buName,a.licence,a.deliverNames,a.state,invId,sum(qty) qty,invName,spec,unit1,unit2 from (
                select a.billNo psNo,a.billDate, invo.billNo,co.name buName,a.deliverNames,a.state, a.licence,(c.qty*c.calNum) qty,c.invId,c.invName,c.spec,c.unit1,c.unit2 from cargo a 
                    inner join cargo_invoice b on a.id=b.cargoId  
                inner join v_main_info c on b.iid=c.iid and a.tenantId in(${tids}) 
                 inner join invoice invo on c.tenantId=invo.tenantId and c.iid=invo.id 
                 inner join contact co on invo.tenantId=co.tenantId and invo.buId=co.id 
              and a.state<>3 ${str} ${bdate} 
            ) a GROUP BY a.psNo,a.billDate, a.billNo,buName,a.licence,a.deliverNames,a.state,invId,invName,spec,unit1,unit2
        ) a  order by state
        `
    let ret = await db.query(sql, {type: QueryTypes.SELECT})
    if(excel) {
        ret.map(row => {
            row.unit = row.unit1
            row.qty = tool.xe.toNumber(row.qty) !== 0 ? (row.qty/row.spec) :0
        })
        let rets = await tool.doExcelOut(ctx,ret,{fileName:'配送明细'},[
            {field: 'psNo', title: '配送号',excelOut: true},
            {field: 'billDate', title: '配送时间',excelOut: true},
            {field: 'billNo', title: '业务票号',excelOut: true},
            {field: 'buName', title: '客户',excelOut: true},
            {field: 'deliverNames', title: '送货人',excelOut: true},
            {field: 'invName', title: '商品',excelOut: true},
            {field: 'qty', title: '数量',excelOut: true},
            {field: 'unit', title: '单位',excelOut: true},
        ])
        return rets
    }
    else {

        return ctx.suc('', ret);
    }
})

const toAssQty = (row) => {
    let m = Math.floor(row.qty/row.spec)
    let m2 = row.qty%row.spec
    let ass = ''
    if(m > 0) ass += m+row.unit1
    if(m2 > 0) ass += ' ' + m2+row.unit2
    return ass
}
module.exports = router;
